Session 2 — Data manipulation and descriptive statistics

Today’s session

This session develops the course strand on Data manipulation and descriptive statistics and connects it to group projects.

Session’s plan

• Import data
• Understand the data
• Clean the data
• Merge datasets
• Reshape datasets
• Inspect data
• Descriptive statistics
• Recode data

Import data

To work on data, you can import existing data in your environment.

• From a local file on your computer
  • read.csv(), load(), read_excel()
• Directly download from a URL

Understand path and relative path

• To import a file, you need to indicate where it is on your computer.

• You can use path and relative path to do that.

• Path show the full way on you computer

• Relative paths show the way from the current working directory.

Set session path

• You can use getwd() to know on which directory your session is running.

• You can change this by specifying a new working directory with setwd

• For me the correct way to set the working directory on my session is

setwd("/Users/maximewalder/Desktop/Teaching_Grading/Applied methods/Session 3")

Import with relative path

• Once in the correct directory, we can uploa the data in the data folder with

read.csv("data/data.csv") # Import the file data.csv from the folder data. 

• You can also always use the complete path.

Exercice 1

Import the data

Understand the data - basics

• What is the unit of analysis?

• What are the class of the different variables?

  • Do you need to change this?

• How many observation, how many variables?

Understand the data - deeper

• How was the data collected?

• What do the variable codes mean?

• How should I analyse the data?

Understand the data - programmatically

• Are variables in the right format?

• Are there caracter ecoding that I need to change?

• What data processing do I need to do to format the data ?

Exercice 2

Inspect the imported data

Clean the data

• Before running operation on your data, you need to understand the data and clean it (oftentime).

• E.g.

  • Missing values
  • Duplicates
  • Variable classes

Exercice 3

Recode variables

Sometime, you need to manipulate the variables so you can have a better grasp at the data.

EX: We want to study the demographic pressure on municipalities in Geneva.

We thus look at th population over-time by municipality, right?

Demographic evaluation by year an municipalities

Recode variables

Trend not so clear for most municipalities.

How did the population change?

Recode to population increase compare to 1950 with the package dplyr

Recode variable with dplyr

data_pop <- data_pop %>% 
  mutate(pop1950 = ifelse(year==1950, pop, NA)) %>% 
  group_by(Mun) %>% 
  mutate(pop1950 = max(pop1950, na.rm=T)) %>% 
  ungroup() %>% 
  mutate(pop_ratio_1950 = pop/pop1950)

Recode variable

Recode variables

Few commands that are helpful to know with dplyr

• %>%
• mutate()
• group_by()
• summarize()
• ifelse() and case_when()

Exercice 4

Merge datasets

• Sometimes, you may want to combine different datasets.

  • Exemple: You want to study crime and compute the number of crime for 1000 inhabitants.
  • One data with the number of crime by municipality
  • One data with the number of people by municipality.

• You can use the merge function to do so, but you need identifiers.

  • What is common between dataset 1 and dataset 2?

Exercice 5

Filter, select and arrange

• You may want to keep only specific observations and/or variables.

• You can subset, and select the columns in your data.

• Advantage: When large data, takes computational power just to handle and work on the dataset

• Disadantage: One reduced, not possible to go back

• Advice: filter and sample your data but always keep the original and manipualtion script.

Filter (observations)

data_filter <- data_pop %>% 
  filter(substr(Mun,1,1) %in% c("A", "B", "C"))
data_filter

# A tibble: 1,560 × 5
   Mun            year   pop pop1950 pop_ratio_1950
   <chr>         <dbl> <dbl>   <dbl>          <dbl>
 1 Aire-la-Ville  1950   144     144          1    
 2 Aire-la-Ville  1951   130     144          0.903
 3 Aire-la-Ville  1952   138     144          0.958
 4 Aire-la-Ville  1953   138     144          0.958
 5 Aire-la-Ville  1954   144     144          1    
 6 Aire-la-Ville  1955   151     144          1.05 
 7 Aire-la-Ville  1956   164     144          1.14 
 8 Aire-la-Ville  1957   166     144          1.15 
 9 Aire-la-Ville  1958   163     144          1.13 
10 Aire-la-Ville  1959   161     144          1.12 
# ℹ 1,550 more rows

Select (variables)

data_select <- data_pop %>% 
  select(contains("av")|contains("mun"))
data_select

# A tibble: 3,588 × 1
   Mun          
   <chr>        
 1 Aire-la-Ville
 2 Aire-la-Ville
 3 Aire-la-Ville
 4 Aire-la-Ville
 5 Aire-la-Ville
 6 Aire-la-Ville
 7 Aire-la-Ville
 8 Aire-la-Ville
 9 Aire-la-Ville
10 Aire-la-Ville
# ℹ 3,578 more rows

Arrange (database)

data_arrange <- data_pop %>% 
  arrange(pop)
data_arrange

# A tibble: 3,588 × 5
   Mun            year   pop pop1950 pop_ratio_1950
   <chr>         <dbl> <dbl>   <dbl>          <dbl>
 1 Aire-la-Ville  1951   130     144          0.903
 2 Gy             1963   135     145          0.931
 3 Gy             1951   137     145          0.945
 4 Aire-la-Ville  1952   138     144          0.958
 5 Aire-la-Ville  1953   138     144          0.958
 6 Gy             1952   139     145          0.959
 7 Gy             1953   139     145          0.959
 8 Gy             1954   143     145          0.986
 9 Gy             1955   143     145          0.986
10 Aire-la-Ville  1950   144     144          1    
# ℹ 3,578 more rows

Exercice 6

Reshape datasets

• Datasets can be in different formats

  • Wide VS long

• What changes is the unit of analyses.

• Important to know what it is, and what I do need for my analyses.

Long data

long_data <- data.frame(mun = c("Mun1", "Mun1", "Mun1",
                              "Mun2", "Mun2", "Mun2"),
                        year = c(1980, 2000, 2020,
                                 1980, 2000, 2020),
                        income = c(1000, 1300, 1290,
                                   2030, 2050, 2100))

long_data

   mun year income
1 Mun1 1980   1000
2 Mun1 2000   1300
3 Mun1 2020   1290
4 Mun2 1980   2030
5 Mun2 2000   2050
6 Mun2 2020   2100

Wide data

wide_data <- data.frame(mun = c("Mun1", "Mun2"),
                        income_1980 = c(1000, 2030),
                        income_2000 = c(1300, 2050),
                        income_2020 = c(1290, 2100))

wide_data

   mun income_1980 income_2000 income_2020
1 Mun1        1000        1300        1290
2 Mun2        2030        2050        2100

Long to wide

wide_data_res <- long_data %>% 
  pivot_wider(
    names_from = year,
    values_from = income,
    names_prefix = "income_"
  )

wide_data_res

# A tibble: 2 × 4
  mun   income_1980 income_2000 income_2020
  <chr>       <dbl>       <dbl>       <dbl>
1 Mun1         1000        1300        1290
2 Mun2         2030        2050        2100

Wide to long

long_data_res <- wide_data %>% 
  pivot_longer(cols = 2:4,
               names_to = "year",
               names_prefix = "income_",
               values_to = "income"
               )

long_data_res

# A tibble: 6 × 3
  mun   year  income
  <chr> <chr>  <dbl>
1 Mun1  1980    1000
2 Mun1  2000    1300
3 Mun1  2020    1290
4 Mun2  1980    2030
5 Mun2  2000    2050
6 Mun2  2020    2100

Exercice 7

Reshape the data_pop object

Descriptive statistics

• Type of variables

• Univariates statistics

• Population VS sample

Type of variables

• Metric (numeric)

• Ordinal

• Categorical

Dummy variables

     Name  Pet
1  Alison  Cat
2   André Fish
3 Bastien  Dog
4   Jason  Dog
5   Elena  Dog
6  Oriane  Cat

Dummy variables

     Name  Pet Fish Cat Dog
1  Alison  Cat    0   1   0
2   André Fish    1   0   0
3 Bastien  Dog    0   0   1
4   Jason  Dog    0   0   1
5   Elena  Dog    0   0   1
6  Oriane  Cat    0   1   0

Univariate statistic

• Scope : maximal - minimal value

• Median : 50% of observation

• Quartile /Percentile

• Interquartile space: spread of the middle 50%

• Mean : Sum of n observation divided by n

• Variance: Average deviation from the mean

• Standard deviation: Square root of variance

Univariate statistics in R

The summary function give away lots of univariate statistics

summary(long_data$income)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
   1000    1292    1665    1628    2045    2100 

Univariate statistics in R

mean(long_data$income) # Mean

[1] 1628.333

median(long_data$income) # Median

[1] 1665

var(long_data$income) # Variance

[1] 235736.7

sd(long_data$income) # Standard deviation

[1] 485.5272

quantile(long_data$income, c(.3, .6)) # Quartile (c(.25,.5,.75)) and percentile

 30%  60% 
1295 2030 

min(long_data$income) # Minimal value

[1] 1000

max(long_data$income) # Maximal value

[1] 2100

Exercice 8

Levels of analyses

• Data comes at different levels of analyses.

• There can be individual or aggregate data.

• Static or dynamic data

• Other levels of analyses (e.g. opinion on issue, votes, cantons, even gender or age can be levels of analyses.)

Levels of analyses

• What is the level of analysis of the data_pop?

• One level is the municipality level

• The second level is the time dimension

Two levels, two analyses.

• Does the municipality have a higher income than the cantonal average?

  • Difference across municipalities, for the same year

• Does the current year has an income above or under the average ?

  • Difference across year, for the same municipality

In R

long_data <- long_data %>% 
  group_by(mun) %>% 
  mutate(mun_av = mean(income)) %>% # Avergae by municipality
  ungroup() %>% 
  group_by(year) %>% 
  mutate(year_av = mean(income)) %>% # Average per year
  ungroup() %>% 
  mutate(av.income = mean(income)) # Average for all observation

Different meanings

Exercice 9

Check results of data manipulation

• When performing data manipulation, it is essential to check the results.

• Important to know whether it produced what was expected.

• Beforehand: Knowing what you expect.

• After the manipulation: Check if the desired outcome has been produced.

Check results of data manipulation

• Understand what works and what did not to understand where the problem is.

• Reshape: How many columns, how many variables

• Merge: Observation in data set 1 and 2, number of variables

• Recoding: Keep old variable to check if and where are the issue

Population VS sample

When you do statistics, you study the whole population, or a sample of it.

This changes your certainty about the produced statistics.

The mean of a population is a true parameter.

The mean of a sample is an estimation of the population mean

The normal distribution (Bell curve)

Normal distribution and Galton’s board

Although individual events are random, collectively we observe some patterns

If events are random, they follow a normal distribution.

What is the probability that given what we see, the events are not random?

Galton’s board

Normal distribution and certainty

• Never 100% sure of anything.

• Some threshold: 95%, 99%, 99.9%

• From the bell curv, how many SD do we need to take to be sure at this level?

Normal distribution and certainty

Exercice 10

Exercice 11

Cumulative coding challenge

Group projects

• Discuss what types of data would be best suited for your projects

• Look for the requested data (OCSTAT, OFS, openswiss.data, ask me and see if we can figure it out!)

• Discuss what is the unit of analysis.

• Try to download the data and import it in R, any issue?

• Try operationalizing one DV and one IV

Questions ?